import logging
import uuid
from datetime import datetime
from typing import Dict, Optional, Tuple
from app.utils.db_utils import MySQLHelper

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


class PointService:
    """
    积分管理服务
    处理积分的创建、查询、消耗和充值
    """

    def __init__(self):
        # 使用默认的数据库连接
        self.db_helper = MySQLHelper(database="shopify_app")
        self._ensure_tables_exist()

    def _ensure_tables_exist(self):
        """
        确保积分相关的表存在
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                # 创建店铺积分表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS shop_points (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    shop_domain VARCHAR(255) NOT NULL UNIQUE,
                    total_points INT NOT NULL DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)

                # 创建积分流水表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS point_transactions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    transaction_id VARCHAR(36) NOT NULL,
                    shop_domain VARCHAR(255) NOT NULL,
                    points INT NOT NULL,
                    type ENUM('CONSUMPTION', 'RECHARGE') NOT NULL,
                    description VARCHAR(255),
                    related_service VARCHAR(50),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_shop_domain (shop_domain),
                    INDEX idx_transaction_id (transaction_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
            conn.commit()
        except Exception as e:
            logging.error(f"创建表失败: {e}")
            if conn:
                conn.rollback()
            raise

    def get_shop_points(self, shop_domain: str) -> int:
        """
        获取店铺的当前积分

        Args:
            shop_domain: 店铺域名

        Returns:
            int: 当前积分
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                # 查询店铺积分
                cursor.execute(
                    "SELECT total_points FROM shop_points WHERE shop_domain = %s",
                    (shop_domain,)
                )
                result = cursor.fetchone()

                # 如果店铺不存在，创建新记录
                if not result:
                    cursor.execute(
                        "INSERT INTO shop_points (shop_domain, total_points) VALUES (%s, %s)",
                        (shop_domain, 0)
                    )
                    conn.commit()
                    return 0

                return result["total_points"]
        except Exception as e:
            logging.error(f"获取店铺积分失败: {e}")
            if conn:
                conn.rollback()
            raise

    def calculate_points_needed(self, text: str) -> int:
        """
        根据文本长度计算需要的积分
        规则：每1个字符消耗5积分

        Args:
            text: 需要翻译的文本

        Returns:
            int: 需要的积分数量
        """
        text_length = len(text)
        # 每1个字符消耗5积分
        return text_length * 5

    def consume_points(self, shop_domain: str, points: int, description: str = "",
                       related_service: str = "translation") -> Tuple[bool, str]:
        """
        消耗店铺积分

        Args:
            shop_domain: 店铺域名
            points: 要消耗的积分数量
            description: 交易描述
            related_service: 相关服务

        Returns:
            Tuple[bool, str]: (是否成功, 消息)
        """
        try:
            # 检查积分余额
            current_points = self.get_shop_points(shop_domain)
            if current_points < points:
                return False, f"积分不足，当前积分: {current_points}，需要积分: {points}"

            # 开始事务
            conn = self.db_helper._get_connection()

            try:
                # 扣减积分
                with conn.cursor() as cursor:
                    cursor.execute(
                        "UPDATE shop_points SET total_points = total_points - %s WHERE shop_domain = %s",
                        (points, shop_domain)
                    )

                    # 如果没有更新记录，说明店铺不存在，插入新记录
                    if cursor.rowcount == 0:
                        cursor.execute(
                            "INSERT INTO shop_points (shop_domain, total_points) VALUES (%s, %s)",
                            (shop_domain, -points)  # 插入负值表示欠积分
                        )

                # 提交事务
                conn.commit()

                logging.info(f"店铺 {shop_domain} 成功消耗 {points} 积分")
                return True, f"成功消耗 {points} 积分"

            except Exception as e:
                # 回滚事务
                conn.rollback()
                logging.error(f"消耗积分事务失败: {e}")
                raise
            finally:
                self.db_helper._close_connection(conn)

        except Exception as e:
            logging.error(f"消耗积分失败: {e}")
            return False, f"操作失败: {str(e)}"

    def recharge_points(self, shop_domain: str, points: int, description: str = "",
                        related_service: str = "payment") -> Tuple[bool, str]:
        """
        充值积分

        Args:
            shop_domain: 店铺域名
            points: 要充值的积分数量
            description: 交易描述
            related_service: 相关服务

        Returns:
            Tuple[bool, str]: (是否成功, 消息)
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                # 开始事务
                conn.begin()

                # 确保店铺记录存在
                self.get_shop_points(shop_domain)  # 这个方法会自动创建店铺记录如果不存在

                # 生成交易ID
                transaction_id = str(uuid.uuid4())

                # 更新店铺积分
                cursor.execute(
                    "UPDATE shop_points SET total_points = total_points + %s WHERE shop_domain = %s",
                    (points, shop_domain)
                )

                # 记录交易流水
                cursor.execute(
                    """
                    INSERT INTO point_transactions 
                    (transaction_id, shop_domain, points, type, description, related_service)
                    VALUES (%s, %s, %s, %s, %s, %s)
                    """,
                    (transaction_id, shop_domain, points, "RECHARGE", description, related_service)
                )

                # 提交事务
                conn.commit()

                return True, f"成功充值 {points} 积分"
        except Exception as e:
            logging.error(f"充值积分失败: {e}")
            if conn:
                conn.rollback()
            return False, f"操作失败: {str(e)}"

    def get_transaction_history(self, shop_domain: str, limit: int = 50, offset: int = 0) -> list:
        """
        获取店铺的积分交易历史

        Args:
            shop_domain: 店铺域名
            limit: 返回记录数量限制
            offset: 偏移量

        Returns:
            list: 交易历史记录列表
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT transaction_id, points, type, description, related_service, created_at
                    FROM point_transactions
                    WHERE shop_domain = %s
                    ORDER BY created_at DESC
                    LIMIT %s OFFSET %s
                    """,
                    (shop_domain, limit, offset)
                )
                return cursor.fetchall()
        except Exception as e:
            logging.error(f"获取交易历史失败: {e}")
            raise


# 创建服务实例
point_service = PointService()


# 对外提供的函数
def get_shop_points(shop_domain: str) -> int:
    """获取店铺积分"""
    return point_service.get_shop_points(shop_domain)


def calculate_points_needed(text: str) -> int:
    """计算文本需要的积分"""
    return point_service.calculate_points_needed(text)


def consume_points(shop_domain: str, points: int, description: str = "",
                   related_service: str = "translation") -> Tuple[bool, str]:
    """消耗积分"""
    # 注意：从现在开始，积分消耗不再记录到point_transactions表
    # 积分消耗记录通过translation_point_transactions表单独管理
    return point_service.consume_points(shop_domain, points, description, related_service)


def recharge_points(shop_domain: str, points: int, description: str = "",
                    related_service: str = "payment") -> Tuple[bool, str]:
    """充值积分"""
    return point_service.recharge_points(shop_domain, points, description, related_service)


def get_transaction_history(shop_domain: str, limit: int = 50, offset: int = 0) -> list:
    """获取交易历史"""
    return point_service.get_transaction_history(shop_domain, limit, offset)